﻿Imports System.Data.SqlClient

Public Class BaoCaoHangThangDAO
    Public Shared Function GetAllBaoCaoHangThangDAO() As DataTable
        'Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSach_BaoCaoHangThang"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function

    Public Shared Function GetBaoCaoHangThang_Trong1ThangDAO(ByVal thang As Integer, ByVal nam As Integer) As DataTable
        'Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSach_BaoCaoHangThang_Trong1Thang"

        Dim para As SqlParameter = New SqlParameter("Thang", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = thang
        cmd.Parameters.Add(para)

        para = New SqlParameter("Nam", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = nam
        cmd.Parameters.Add(para)

        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function

    Public Shared Sub InsertBaoCaoHangThangDAO(ByVal BaoCaoHangThang As BaoCaoHangThangDTO)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Insert_BaoCaoHangThang"
        cmd.Connection = cn
        '@MaCongTy int, @TencongTy nvarchar(50), @SoNhaDaDangKy int, @SoNhaDaCoNguoiThue int,
        '@TyLe float, @Thang int, @Nam int
        cmd.Connection.Open()


        Dim para As SqlParameter = New SqlParameter("MaCongTy", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.MaCongTy
        cmd.Parameters.Add(para)

        para = New SqlParameter("TencongTy", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.TenCongTy
        cmd.Parameters.Add(para)

        para = New SqlParameter("SoNhaDaDangKy", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.SoNhaDangKy
        cmd.Parameters.Add(para)

        para = New SqlParameter("SoNhaDaCoNguoiThue", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.SoNhaDaCoNguoiThue
        cmd.Parameters.Add(para)

        para = New SqlParameter("TyLe", SqlDbType.Float)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.TyLe
        cmd.Parameters.Add(para)

        para = New SqlParameter("Thang", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.Thang
        cmd.Parameters.Add(para)

        para = New SqlParameter("Nam", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.Nam
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Sub UpdateBaoCaoHangThangDAO(ByVal BaoCaoHangThang As BaoCaoHangThangDTO)
        'Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Update_BaoCaoHangThang"
        cmd.Connection = cn
        cmd.Connection.Open()




        Dim para As SqlParameter = New SqlParameter("STT", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.STT
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaCongTy", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.MaCongTy
        cmd.Parameters.Add(para)
      

        para = New SqlParameter("TencongTy", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.TenCongTy
        cmd.Parameters.Add(para)

        para = New SqlParameter("SoNhaDaDangKy", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.SoNhaDangKy
        cmd.Parameters.Add(para)

        para = New SqlParameter("SoNhaDaCoNguoiThue", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.SoNhaDaCoNguoiThue
        cmd.Parameters.Add(para)

        para = New SqlParameter("TyLe", SqlDbType.Float)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.TyLe
        cmd.Parameters.Add(para)

        para = New SqlParameter("Thang", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.Thang
        cmd.Parameters.Add(para)

        para = New SqlParameter("Nam", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = BaoCaoHangThang.Nam
        cmd.Parameters.Add(para)


        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub


    Public Shared Sub DeleteBaoCaoHangThangDAO(ByVal thang As Integer, ByVal nam As Integer)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Delete_BaoCaoHangThang"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("Thang", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = thang
        cmd.Parameters.Add(para)

        para = New SqlParameter("Nam", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = nam
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub
End Class
